package dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.Students;

public class StudentMod {
	Connection connection;
	private static StudentMod isntanceSD;
	public static StudentMod getIsntance(){
		if(isntanceSD == null){
			isntanceSD = new StudentMod();
		}
		return isntanceSD;
	}
	public boolean isSID(int sid) throws IOException{ /*kiem tra tinh hop le cua nid va co nam trong DB hay khong*/
		connection =  DB_Connection.getIsntance().getConnection();
		boolean result = false;
		PreparedStatement sql = null;
		try {
			sql = connection.prepareStatement("SELECT sid FROM students where sid = ? ");
			sql.setInt(1, sid);
			ResultSet rs = sql.executeQuery();
			while (rs.next()) {
				result = true;
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	public boolean addStudent(Students students){
		boolean result = false;
		PreparedStatement sql = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			sql = connection.prepareStatement("INSERT INTO students ( pid, idstudents, name, sex, birthday, address, bio, avartar) " +
					"VALUES (? , ? , ? , ? , ? , ? , ? , ? ) LIMIT = 1 ");
			sql.setInt(1, students.getPid());
			sql.setString(2, students.getIdstudents());
			sql.setString(3,students.getName());
			sql.setInt(4, students.getSex());
			sql.setDate(5, students.getBirthday());
			sql.setString(6, students.getAddress());
			sql.setString(7, students.getBio());
			sql.setString(8,students.getAvartar());
			int rs = sql.executeUpdate();
			if(rs>0) result = true;
			sql.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public Students getByPid(int p){
		Students result =null;
		PreparedStatement sql = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			sql = connection.prepareStatement("SELECT sid, pid, idstudents, name, sex, birthday, address, bio, avartar FROM students WHERE pid = ? ");
			sql.setInt(1, p);
			ResultSet rs = sql.executeQuery();
			while(rs.next()){
				int sid = rs.getInt(1);
				int pid = rs.getInt(2);
				String idstudents = rs.getString(3);
				String name = rs.getString(4);
				int sex = rs.getInt(5);
				Date birthday = rs.getDate(6);
				String address =rs.getString(7);
				String bio =rs.getString(8);
				String avartar = rs.getString(9);
				result = new Students(sid, pid, idstudents, name, sex, birthday, address, bio, avartar);
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public Students getBySid(int s){
		Students result =null;
		PreparedStatement sql = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			sql = connection.prepareStatement("SELECT sid, pid, idstudents, name, sex, birthday, address, bio, avartar FROM students WHERE sid = ? ");
			sql.setInt(1, s);
			ResultSet rs = sql.executeQuery();
			while(rs.next()){
				int sid = rs.getInt(1);
				int pid = rs.getInt(2);
				String idstudents = rs.getString(3);
				String name = rs.getString(4);
				int sex = rs.getInt(5);
				Date birthday = rs.getDate(6);
				String address =rs.getString(7);
				String bio =rs.getString(8);
				String avartar = rs.getString(9);
				result = new Students(sid, pid, idstudents, name, sex, birthday, address, bio, avartar);
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public Students getByIdStudent(String ids){
		Students result =null;
		PreparedStatement sql = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			sql = connection.prepareStatement("SELECT sid, pid, idstudents, name, sex, birthday, address, bio, avartar FROM students WHERE idstudents = ? ");
			sql.setString(1, ids);
			ResultSet rs = sql.executeQuery();
			while(rs.next()){
				int sid = rs.getInt(1);
				int pid = rs.getInt(2);
				String idstudents = rs.getString(3);
				String name = rs.getString(4);
				int sex = rs.getInt(5);
				Date birthday = rs.getDate(6);
				String address =rs.getString(7);
				String bio =rs.getString(8);
				String avartar = rs.getString(9);
				result = new Students(sid, pid, idstudents, name, sex, birthday, address, bio, avartar);
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean UpdateByPid(Students students, int p){
		boolean result = false;
		PreparedStatement sql = null;
		try {
			
			connection = DB_Connection.getIsntance().getConnection();
			String sqlu = "UPDATE students SET pid = ? , " +
					" idstudents = ? , name = ? , sex = ? ," +
					" birthday = ? , address = ? , bio = ? , avartar = ? " +
					" WHERE pid = ? LIMIT = 1 ";
			sql = connection.prepareStatement(sqlu);
			sql.setInt(1, students.getPid());
			sql.setString(2, students.getIdstudents());
			sql.setString(3, students.getName());
			sql.setInt(4, students.getSex());
			sql.setDate(5, students.getBirthday());
			sql.setString(6, students.getAddress());
			sql.setString(7, students.getBio());
			sql.setString(8, students.getAvartar());
			sql.setInt(9, p );
			int number = sql.executeUpdate();
			if (number > 0) {
				result = true;
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean UpdateBySid(Students students, int s){
		boolean result = false;
		PreparedStatement sql = null;
		try {
			
			connection = DB_Connection.getIsntance().getConnection();
			String sqlu = "UPDATE students SET pid = ? , " +
					" idstudents = ? , name = ? , sex = ? ," +
					" birthday = ? , address = ? , bio = ? , avartar = ? " +
					" WHERE sid = ? LIMIT = 1 ";
			sql = connection.prepareStatement(sqlu);
			sql.setInt(1, students.getPid());
			sql.setString(2, students.getIdstudents());
			sql.setString(3, students.getName());
			sql.setInt(4, students.getSex());
			sql.setDate(5, students.getBirthday());
			sql.setString(6, students.getAddress());
			sql.setString(7, students.getBio());
			sql.setString(8, students.getAvartar());
			sql.setInt(9, s );
			int number = sql.executeUpdate();
			if (number > 0) {
				result = true;
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean UpdateByIdStudent(Students students, int id){
		boolean result = false;
		PreparedStatement sql = null;
		try {
			
			connection = DB_Connection.getIsntance().getConnection();
			String sqlu = "UPDATE students SET pid = ? , " +
					" idstudents = ? , name = ? , sex = ? ," +
					" birthday = ? , address = ? , bio = ? , avartar = ? " +
					" WHERE idstudents = ? LIMIT = 1 ";
			sql = connection.prepareStatement(sqlu);
			sql.setInt(1, students.getPid());
			sql.setString(2, students.getIdstudents());
			sql.setString(3, students.getName());
			sql.setInt(4, students.getSex());
			sql.setDate(5, students.getBirthday());
			sql.setString(6, students.getAddress());
			sql.setString(7, students.getBio());
			sql.setString(8, students.getAvartar());
			sql.setInt(9, id );
			int number = sql.executeUpdate();
			if (number > 0) {
				result = true;
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	 public boolean delBySid(int sid){
			PreparedStatement delpd = null;
			boolean result = false;
			Connection connection = DB_Connection.getIsntance().getConnection();
			try {
				delpd = connection.prepareStatement("DELETE FROM students WHERE sid = ? LIMIT 1");
				delpd.setInt(1, sid);
				int number = delpd.executeUpdate();
				if (number > 0) {
					result = true;
				}
				delpd.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return  result;
		}
	 public boolean delByIdStudent(String id){
			PreparedStatement delpd = null;
			boolean result = false;
			Connection connection = DB_Connection.getIsntance().getConnection();
			try {
				delpd = connection.prepareStatement("DELETE FROM students WHERE idstudents = ? LIMIT 1");
				delpd.setString(1, id);
				int number = delpd.executeUpdate();
				if (number > 0) {
					result = true;
				}
				delpd.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return  result;
		}
}
